package com.freedomlane.pgen;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;


/**
 * This class is a collection of database access and management methods that are
 * used by the PasswordGenerator.  I have left it final so that it is not to be
 * extended.  Also I have used to a private constructor to make sure that no
 * instances of this class are ever created.  This class serves as a container
 * for a set of static methods providing required services.
 *
 * @author Sandeep Ellawala
 *
 * @version 1.0
 *
 * @since 1.0
 */
public final class DBAccess {
    private final static String DB_CLASS = "smallsql.server.SSDriver";

    private final static String DB_URL = "jdbc:smallsql:pwddb";
    
    private DBAccess() {
        //This makes sure that no instances of this class are to be created.
    }


    /**
     * Check if a given password is already created once, and a record of it is
     * available in the database or not.
     *
     * @param password The password to be checked for getting repeated.
     *
     * @return True if the password had already been created once and a record
     * of it exists in the database.
     *
     * @return False if the password had not already been created once and a
     * record of it does not exist in the database.
     */
    public final static boolean isAlreadyCreated(String password) {
        if (password == null) {
            return false;
        }

        if (!isDatabaseAvailable()) {
            //There is no database and I cannot proceed beyond this point with
            //no database.  I don't see any reason for me to create a database
            //as well.

            return false;
        }
        
        boolean found = false;

        Connection con = null;

        try {
            Class.forName(DB_CLASS);

            con = DriverManager.getConnection(DB_URL);

            String query = "SELECT * FROM pwdtbl WHERE upass = '" + password +
                    "'";

            Statement stmt = con.createStatement();

            ResultSet rs = stmt.executeQuery(query);

            if (rs.next()) {
                found = true;
            }
        } catch (ClassNotFoundException exc) {
            found = false;
        } catch (SQLException exc) {
            found = false;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (Exception e) {
                    //Ignore
                }
            }
        }

        return found;
    }


    /**
     * Insert a given password into the database to maintain a record of all the
     * passwords ever created.  This feature helps maintain a non-repeatable
     * policy for all passwords.
     *
     * @param password The password to be inserted into the database as a
     * password that has already been created once.
     */
    public final static void recordPassword(String password) {
        if (password == null) {
            return;
        }

        if (!isDatabaseAvailable()) {
            //Database is not available.  I must create a database before I
            //proceed further.

            if (!createDatabase(false)) {
                //There is no database available and I cannot seem to create one
                //as well.  No option but to return since I cannot proceed with
                //these conditions.

                return;
            }
        }

        Connection con = null;

        try {
            Class.forName(DB_CLASS);

            con = DriverManager.getConnection(DB_URL);

            Statement stmt = con.createStatement();

            String query = "INSERT INTO pwdtbl (upass) VALUES ('" +
                    password + "')";

            stmt.executeUpdate(query);
        } catch (ClassNotFoundException exc) {
            //Ignore
        } catch (SQLException exc) {
            //Ignore
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (Exception e) {
                    //Ignore
                }
            }
        }
    }


    /**
     * Check if the database for passwords stroage is available or not.  As
     * Ackham's Razor would suggest when there is no database, one must be
     * created in a later step.
     *
     * @return True if the database for passwords storage is available.  You can
     * proceed to store your passwords, or check for repeatability.
     *
     * @return False if the database for passwords storage is not available.
     * You will have to create the database before either storing passwords or
     * check for repeatability.
     */
    private final static boolean isDatabaseAvailable() {
        boolean available = false;

        Connection con = null;

        try {
            Class.forName(DB_CLASS);

            //If there is no database, trying to connect to it would definitely
            //generate an exception.
            con = DriverManager.getConnection(DB_URL);

            available = true;

            con.close();
        } catch (ClassNotFoundException exc) {
            available = false;
        } catch (SQLException exc) {
            available = false;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (Exception e) {
                    //Ignore
                }
            }
        }

        return available;
    }


    /**
     * Create either a new database only when there is no existing database, or
     * create a new database by dropping an existing database.
     * 
     * @param dropCurrent Set to true if the new database should be created even
     * if a database already exists.  The new database will be created after the
     * existing database had been dropped.
     *
     * @return True if a new database was created successfully.
     *
     * @return False if a new database could not be created successfully.
     */
    private final static boolean createDatabase(boolean dropCurrent) {
        boolean created = false;

        Connection con = null;

        if (!isDatabaseAvailable()) {
            //Only have to create a new datbase.  No need to worry about having
            //to drop an existing database.

            try {
                Class.forName(DB_CLASS);

                con = DriverManager.getConnection("jdbc:smallsql");

                Statement stmt = con.createStatement();

                String query = "CREATE DATABASE pwddb";

                int res = stmt.executeUpdate(query);

                if (res <= 0) {
                    throw new DBException();
                }

                created = true;
            } catch (ClassNotFoundException exc) {
                created = false;
            } catch (SQLException exc) {
                created = false;
            } catch (DBException exc) {
                created = false;
            } finally {
                if (con != null) {
                    try {
                        con.close();
                    } catch (Exception e) {
                        //Ignore
                    }
                }
            }
        } else if (dropCurrent) {
            //We know that there is a database available, and it must be dropped
            //to create a new one.

            try {
                Class.forName(DB_CLASS);

                con = DriverManager.getConnection("jdbc:smallsql");

                String query = "DROP DATABASE pwddb";

                Statement stmt = con.createStatement();

                int res = stmt.executeUpdate(query);

                if (res <= 0) {
                    throw new DBException();
                }

                query = "CREATE DATABASE pwddb";

                stmt = con.createStatement();

                res = stmt.executeUpdate(query);

                if (res <= 0) {
                    throw new DBException();
                }

                created = true;
            } catch (ClassNotFoundException exc) {
                created = false;
            } catch (SQLException exc) {
                created = false;
            } catch (DBException exc) {
                created = false;
            } finally {
                if (con != null) {
                    try {
                        con.close();
                    } catch (Exception e) {
                        //Ignore
                    }
                }
            }
        }

        if (created) {
            //The databse is created by this point.  Now the table must be
            //created for storing passwords as well.

            try {
                Class.forName(DB_CLASS);

                con = DriverManager.getConnection(DB_URL);

                Statement stmt = con.createStatement();

                String query = "CREATE TABLE pwdtbl (upass VARCHAR(50))";

                int res = stmt.executeUpdate(query);

                if (res > 0) {
                    created = true;
                }
            } catch (ClassNotFoundException exc) {
                created = false;
            } catch (SQLException exc) {
                created = false;
            } finally {
                if (con != null) {
                    try {
                        con.close();
                    } catch (Exception e) {
                        //Ignore
                    }
                }
            }
        }

        return created;
    }
}